import openpyxl
import re


def extract_numbers_from_cell(text):
    text = str(text)
    star_pos = text.find('*')

    # 初始化结果
    before_num = None
    after_num = None

    # 处理星号前部分（从右向左找第一个数字）
    if star_pos != -1:
        before_part = text[:star_pos]
        reversed_before = before_part[::-1]
        before_match = re.search(r'\d+', reversed_before)
        if before_match:
            before_num = int(before_match.group()[::-1])  # 反转回正常顺序

        # 处理星号后部分（正常从左向右找第一个数字）
        after_part = text[star_pos + 1:]
        after_match = re.search(r'\d+', after_part)
        if after_match:
            after_num = int(after_match.group())

    return before_num, after_num


def process_excel(input_path, output_path):
    wb = openpyxl.load_workbook(input_path)
    ws = wb.active

    for row in ws.iter_rows(min_row=1, max_col=6):  # F列是第6列
        f_cell = row[5]  # 索引从0开始，第6列是索引5

        # 处理当前单元格
        before_num, after_num = extract_numbers_from_cell(f_cell.value)

        # 写入G列（第7列）和H列（第8列）
        ws.cell(row=f_cell.row, column=7).value = before_num
        ws.cell(row=f_cell.row, column=8).value = after_num

    wb.save(output_path)
    print(f"处理完成，结果已保存至：{output_path}")


# 使用示例
if __name__ == "__main__":
    input_file = "C:\\Users\\Administrator\\Desktop\\111.xlsx"
    output_file = "C:\\Users\\Administrator\\Desktop\\output.xlsx"
    process_excel(input_file, output_file)